Create a Web Application for an ETF Analyzer¶

In this Challenge assignment, you’ll build a financial database and web application by using SQL, Python, and the Voilà library to analyze the performance of a hypothetical fintech ETF.

Instructions:

Use this notebook to complete your analysis of a fintech ETF that consists of four stocks: GOST, GS, PYPL, and SQ. Each stock has its own table in the etf.db database, which the Starter_Code folder also contains.

Analyze the daily returns of the ETF stocks both individually and as a whole. Then deploy the visualizations to a web application by using the Voilà library.

The detailed instructions are divided into the following parts:

  • Analyze a single asset in the ETF

  • Optimize data access with Advanced SQL queries

  • Analyze the ETF portfolio

  • Deploy the notebook as a web application

Analyze a Single Asset in the ETF¶

For this part of the assignment, you’ll use SQL queries with Python, Pandas, and hvPlot to analyze the performance of a single asset from the ETF.

Complete the following steps:

  1. Write a SQL SELECT statement by using an f-string that reads all the PYPL data from the database. Using the SQL SELECT statement, execute a query that reads the PYPL data from the database into a Pandas DataFrame.

  2. Use the head and tail functions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis.

  3. Using hvPlot, create an interactive visualization for the PYPL daily returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.

  4. Using hvPlot, create an interactive visualization for the PYPL cumulative returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.

Optimize Data Access with Advanced SQL Queries¶

For this part of the assignment, you’ll continue to analyze a single asset (PYPL) from the ETF. You’ll use advanced SQL queries to optimize the efficiency of accessing data from the database.

Complete the following steps:

  1. Access the closing prices for PYPL that are greater than 200 by completing the following steps:

    • Write a SQL SELECT statement to select the dates where the PYPL closing price was higher than 200.0.

    • Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.

    • Select the “time” and “close” columns for those dates where the closing price was higher than 200.0.

  2. Find the top 10 daily returns for PYPL by completing the following steps:

    • Write a SQL statement to find the top 10 PYPL daily returns. Make sure to do the following:

      • Use SELECT to select only the “time” and “daily_returns” columns.

      • Use ORDER to sort the results in descending order by the “daily_returns” column.

      • Use LIMIT to limit the results to the top 10 daily return values.

    • Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.

Analyze the ETF Portfolio¶

For this part of the assignment, you’ll build the entire ETF portfolio and then evaluate its performance. To do so, you’ll build the ETF portfolio by using SQL joins to combine all the data for each asset.

Complete the following steps:

  1. Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:

    • Use a SQL inner join to join each table on the “time” column. Access the “time” column in the GDOT table via the GDOT.time syntax. Access the “time” columns from the other tables via similar syntax.

    • Using the SQL query, read the data from the database into a Pandas DataFrame. Review the resulting DataFrame.

  2. Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.

    Hint Assuming that this ETF contains equally weighted returns, you can average the returns for each asset to get the average returns of the portfolio. You can then use the average returns of the portfolio to calculate the annualized returns and the cumulative returns. For the calculation to get the average daily returns for the portfolio, use the following code:

    etf_portfolio_returns = etf_portfolio['daily_returns'].mean(axis=1)

    You can use the average daily returns of the portfolio the same way that you used the daily returns of a single asset.

  3. Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.

Hint To calculate the annualized returns, multiply the mean of the etf_portfolio_returns values by 252.

To convert the decimal values to percentages, multiply the results by 100.

  1. Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the cumulative returns of the ETF portfolio.

  2. Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.

Deploy the Notebook as a Web Application¶

For this part of the assignment, complete the following steps:

  1. Use the Voilà library to deploy your notebook as a web application. You can deploy the web application locally on your computer.

  2. Take a screen recording or screenshots to show how the web application appears when using Voilà. Include the recording or screenshots in the README.md file for your GitHub repository.

Review the following code which imports the required libraries, initiates your SQLite database, popluates the database with records from the etf.db seed file that was included in your Starter_Code folder, creates the database engine, and confirms that data tables that it now contains.¶

In [1]:
# Importing the required libraries and dependencies
import numpy as np
import pandas as pd
import hvplot.pandas
import sqlalchemy

# Create a temporary SQLite database and populate the database with content from the etf.db seed file
database_connection_string = 'sqlite:///etf.db'

# Create an engine to interact with the SQLite database
engine = sqlalchemy.create_engine(database_connection_string)

# Confirm that table names contained in the SQLite database.
engine.table_names()

C:\Users\thest\anaconda3\envs\dev\lib\site-packages\ipykernel_launcher.py:14: SADeprecationWarning: The Engine.table_names() method is deprecated and will be removed in a future release.  Please refer to Inspector.get_table_names(). (deprecated since: 1.4)
Out[1]:
['GDOT', 'GS', 'PYPL', 'SQ']

Analyze a single asset in the FinTech ETF¶

For this part of the assignment, you’ll use SQL queries with Python, Pandas, and hvPlot to analyze the performance of a single asset from the ETF.

Complete the following steps:

  1. Write a SQL SELECT statement by using an f-string that reads all the PYPL data from the database. Using the SQL SELECT statement, execute a query that reads the PYPL data from the database into a Pandas DataFrame.

  2. Use the head and tail functions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis.

  3. Using hvPlot, create an interactive visualization for the PYPL daily returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.

  4. Using hvPlot, create an interactive visualization for the PYPL cumulative returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.

Step 1: Write a SQL SELECT statement by using an f-string that reads all the PYPL data from the database. Using the SQL SELECT statement, execute a query that reads the PYPL data from the database into a Pandas DataFrame.¶

In [2]:
# Write a SQL query to SELECT all of the data from the PYPL table
query = """
    SELECT * FROM PYPL;
"""
results = engine.execute(query)

# Use the query to read the PYPL data into a Pandas DataFrame
pypl_dataframe = pd.read_sql_table('PYPL', con=engine)

Step 2: Use the head and tail functions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis.¶

In [3]:
# View the first 5 rows of the DataFrame.
pypl_dataframe.head()
Out[3]:
time open high low close volume daily_returns
0 2016-12-16 39.90 39.90 39.12 39.32 7298861 -0.005564
1 2016-12-19 39.40 39.80 39.11 39.45 3436478 0.003306
2 2016-12-20 39.61 39.74 39.26 39.74 2940991 0.007351
3 2016-12-21 39.84 40.74 39.82 40.09 5826704 0.008807
4 2016-12-22 40.04 40.09 39.54 39.68 4338385 -0.010227
In [4]:
# View the last 5 rows of the DataFrame.
pypl_dataframe.tail()
Out[4]:
time open high low close volume daily_returns
994 2020-11-30 212.51 215.83 207.0900 214.200 8992681 0.013629
995 2020-12-01 217.15 220.57 214.3401 216.520 9148174 0.010831
996 2020-12-02 215.60 215.75 210.5000 212.660 6414746 -0.017827
997 2020-12-03 213.33 216.93 213.1100 214.680 6463339 0.009499
998 2020-12-04 214.88 217.28 213.0100 217.235 2118319 0.011901

Step 3: Using hvPlot, create an interactive visualization for the PYPL daily returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶

In [5]:
# Create an interactive visualization with hvplot to plot the daily returns for PYPL.
pypl_dataframe.hvplot(
    x='time',
    xlabel='Date', 
    y='daily_returns', 
    ylabel='Daily Returns', 
    title='PYPL Daily Returns',
)
Out[5]:

Step 4: Using hvPlot, create an interactive visualization for the PYPL cumulative returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶

In [6]:
# Create an interactive visaulization with hvplot to plot the cumulative returns for PYPL.
pypl_dataframe['cumulative_returns'] = pypl_dataframe['daily_returns'].cumsum()

pypl_dataframe.hvplot(
    x='time',
    xlabel='Date', 
    y='cumulative_returns', 
    ylabel='Cumulative Returns', 
    title='PYPL Cumulative Returns',
)
Out[6]:

Optimize the SQL Queries¶

For this part of the assignment, you’ll continue to analyze a single asset (PYPL) from the ETF. You’ll use advanced SQL queries to optimize the efficiency of accessing data from the database.

Complete the following steps:

  1. Access the closing prices for PYPL that are greater than 200 by completing the following steps:

  2. Access the closing prices for PYPL that are greater than 200 by completing the following steps:

    • Write a SQL SELECT statement to select the dates where the PYPL closing price was higher than 200.0.

    • Select the “time” and “close” columns for those dates where the closing price was higher than 200.0.

    • Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.

  3. Find the top 10 daily returns for PYPL by completing the following steps:

    • Write a SQL statement to find the top 10 PYPL daily returns. Make sure to do the following:

      • Use SELECT to select only the “time” and “daily_returns” columns.

      • Use ORDER to sort the results in descending order by the “daily_returns” column.

      • Use LIMIT to limit the results to the top 10 daily return values.

    • Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.

Step 1: Access the closing prices for PYPL that are greater than 200 by completing the following steps:¶

- Write a SQL `SELECT` statement to select the dates where the PYPL closing price was higher than 200.0.

- Select the “time” and “close” columns for those dates where the closing price was higher than 200.0.

- Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.
In [7]:
# Write a SQL SELECT statement to select the time and close columns 
# where the PYPL closing price was higher than 200.0.
close_price_above_200 = """
SELECT time, close
FROM PYPL
WHERE close > 200;
"""
results_above200 = engine.execute(close_price_above_200)


# Using the query, read the data from the database into a Pandas DataFrame
pypl_higher_than_200df = pd.read_sql(close_price_above_200, con=engine)

# Review the resulting DataFrame
display(pypl_higher_than_200df.head())
time close
0 2020-08-05 00:00:00.000000 202.92
1 2020-08-06 00:00:00.000000 204.09
2 2020-08-25 00:00:00.000000 201.71
3 2020-08-26 00:00:00.000000 203.53
4 2020-08-27 00:00:00.000000 204.34

Step 2: Find the top 10 daily returns for PYPL by completing the following steps:¶

-  Write a SQL statement to find the top 10 PYPL daily returns. Make sure to do the following:

    * Use `SELECT` to select only the “time” and “daily_returns” columns.

    * Use `ORDER` to sort the results in descending order by the “daily_returns” column.

    * Use `LIMIT` to limit the results to the top 10 daily return values.

- Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.
In [8]:
# Write a SQL SELECT statement to select the time and daily_returns columns
# Sort the results in descending order and return only the top 10 return values
pypl_top_10_sql = """
SELECT time, daily_returns
FROM PYPL
ORDER BY daily_returns DESC
LIMIT 10;
"""
top_10_results = engine.execute(pypl_top_10_sql)


# Using the query, read the data from the database into a Pandas DataFrame
pypl_top_10_returns = pd.read_sql(pypl_top_10_sql, con=engine)

# Review the resulting DataFrame
display(pypl_top_10_returns.head(10))
time daily_returns
0 2020-03-24 00:00:00.000000 0.140981
1 2020-05-07 00:00:00.000000 0.140318
2 2020-03-13 00:00:00.000000 0.138700
3 2020-04-06 00:00:00.000000 0.100877
4 2018-10-19 00:00:00.000000 0.093371
5 2019-10-24 00:00:00.000000 0.085912
6 2020-11-04 00:00:00.000000 0.080986
7 2020-03-10 00:00:00.000000 0.080863
8 2020-04-22 00:00:00.000000 0.075321
9 2018-12-26 00:00:00.000000 0.074656

Analyze the Fintech ETF Portfolio¶

For this part of the assignment, you’ll build the entire ETF portfolio and then evaluate its performance. To do so, you’ll build the ETF portfolio by using SQL joins to combine all the data for each asset.

Complete the following steps:

  1. Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:

    • Use a SQL inner join to join each table on the “time” column. Access the “time” column in the GDOT table via the GDOT.time syntax. Access the “time” columns from the other tables via similar syntax.

    • Using the SQL query, read the data from the database into a Pandas DataFrame. Review the resulting DataFrame.

  2. Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.

    Hint Assuming that this ETF contains equally weighted returns, you can average the returns for each asset to get the average returns of the portfolio. You can then use the average returns of the portfolio to calculate the annualized returns and the cumulative returns. For the calculation to get the average daily returns for the portfolio, use the following code:

    etf_portfolio_returns = etf_portfolio['daily_returns'].mean(axis=1)

    You can use the average daily returns of the portfolio the same way that you used the daily returns of a single asset.

  3. Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.

Hint To calculate the annualized returns, multiply the mean of the etf_portfolio_returns values by 252.

To convert the decimal values to percentages, multiply the results by 100.

  1. Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the cumulative returns of the ETF portfolio.

  2. Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.

Step 1: Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:¶

- Use a SQL inner join to join each table on the “time” column. Access the “time” column in the `GDOT` table via the `GDOT.time` syntax. Access the “time” columns from the other tables via similar syntax.

- Using the SQL query, read the data from the database into a Pandas DataFrame. Review the resulting DataFrame.
In [9]:
# Wreate a SQL query to join each table in the portfolio into a single DataFrame 
# Use the time column from each table as the basis for the join
query = """
SELECT * 
FROM GDOT 
INNER JOIN GS
    ON GDOT.time=GS.time
INNER JOIN PYPL
    ON GDOT.time=PYPL.time
INNER JOIN SQ
    ON GDOT.time=SQ.time
"""

# Using the query, read the data from the database into a Pandas DataFrame
etf_portfolio = pd.read_sql_query(query, con=engine)


# Review the resulting DataFrame
display(etf_portfolio.head())
display(etf_portfolio.tail())
time open high low close volume daily_returns time open high ... close volume daily_returns time open high low close volume daily_returns
0 2016-12-16 00:00:00.000000 24.41 24.73 23.94 23.980 483544 -0.023218 2016-12-16 00:00:00.000000 242.80 243.19 ... 39.32 7298861 -0.005564 2016-12-16 00:00:00.000000 14.29 14.47 14.2300 14.375 4516341 0.017339
1 2016-12-19 00:00:00.000000 24.00 24.01 23.55 23.790 288149 -0.007923 2016-12-19 00:00:00.000000 238.34 239.74 ... 39.45 3436478 0.003306 2016-12-19 00:00:00.000000 14.34 14.60 14.3000 14.360 3944657 -0.001043
2 2016-12-20 00:00:00.000000 23.75 23.94 23.58 23.820 220341 0.001261 2016-12-20 00:00:00.000000 240.52 243.65 ... 39.74 2940991 0.007351 2016-12-20 00:00:00.000000 14.73 14.82 14.4100 14.490 5207412 0.009053
3 2016-12-21 00:00:00.000000 23.90 23.97 23.69 23.860 249189 0.001679 2016-12-21 00:00:00.000000 242.24 242.40 ... 40.09 5826704 0.008807 2016-12-21 00:00:00.000000 14.45 14.54 14.2701 14.380 3901738 -0.007591
4 2016-12-22 00:00:00.000000 23.90 24.01 23.70 24.005 383139 0.006077 2016-12-22 00:00:00.000000 241.23 242.86 ... 39.68 4338385 -0.010227 2016-12-22 00:00:00.000000 14.33 14.34 13.9301 14.040 3874004 -0.023644

5 rows × 28 columns

time open high low close volume daily_returns time open high ... close volume daily_returns time open high low close volume daily_returns
994 2020-11-30 00:00:00.000000 55.87 56.0899 53.1100 53.55 361004 -0.043750 2020-11-30 00:00:00.000000 232.05 235.0000 ... 214.200 8992681 0.013629 2020-11-30 00:00:00.000000 213.00 213.5400 200.23 210.9800 10485040 -0.007153
995 2020-12-01 00:00:00.000000 54.00 54.2500 52.0007 53.79 546792 0.004482 2020-12-01 00:00:00.000000 231.96 234.8704 ... 216.520 9148174 0.010831 2020-12-01 00:00:00.000000 212.56 212.9500 200.50 203.0000 9911165 -0.037823
996 2020-12-02 00:00:00.000000 53.20 53.7900 50.9400 52.32 479868 -0.027328 2020-12-02 00:00:00.000000 232.08 238.1300 ... 212.660 6414746 -0.017827 2020-12-02 00:00:00.000000 200.13 204.0596 195.13 202.1100 9968056 -0.004384
997 2020-12-03 00:00:00.000000 52.48 54.1600 52.0200 53.76 474175 0.027523 2020-12-03 00:00:00.000000 237.29 238.8500 ... 214.680 6463339 0.009499 2020-12-03 00:00:00.000000 207.00 208.6900 204.05 205.5300 6497836 0.016921
998 2020-12-04 00:00:00.000000 53.87 54.1900 53.0850 53.86 83596 0.001860 2020-12-04 00:00:00.000000 237.70 239.6400 ... 217.235 2118319 0.011901 2020-12-04 00:00:00.000000 206.29 208.9500 203.58 207.6164 2820542 0.010151

5 rows × 28 columns

Step 2: Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.¶

In [18]:
# Create a DataFrame that displays the mean value of the “daily_returns” columns for all four assets.
average_returns = (etf_portfolio['daily_returns']).mean(axis=1)

#Made two datasets with info needed  and got rid of duplicate 'time' columns.
time_dataset = etf_portfolio.loc[:, ['time']]
time_dataset = time_dataset.loc[:,~time_dataset.columns.duplicated()]

#Used concat to join the two dataframes.
etf_portfolio_returns = pd.concat([time_dataset, average_returns], axis=1, join='inner')

#renamed columns to make them clearer, and dropped the time component
etf_portfolio_returns_cols = ['time', 'average_daily_returns']
etf_portfolio_returns.columns = etf_portfolio_returns_cols
etf_portfolio_returns['time'] = pd.to_datetime(etf_portfolio_returns['time'])

#Review the resulting DataFrame
display(etf_portfolio_returns)
time average_daily_returns
0 2016-12-16 -0.007038
1 2016-12-19 -0.001216
2 2016-12-20 0.008567
3 2016-12-21 -0.001004
4 2016-12-22 -0.008243
... ... ...
994 2020-11-30 -0.014635
995 2020-12-01 -0.003990
996 2020-12-02 -0.006288
997 2020-12-03 0.011246
998 2020-12-04 0.009108

999 rows × 2 columns

Step 3: Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.¶

In [11]:
# Use the average daily returns provided by the etf_portfolio_returns DataFrame 
# to calculate the annualized return for the portfolio. 
annualized_etf_portfolio_returns = etf_portfolio_returns.mean() * 252

# Display the annualized return value of the ETF portfolio.
print(f"Annualized {annualized_etf_portfolio_returns}")

Annualized average_daily_returns    0.438273
dtype: float64

C:\Users\thest\anaconda3\envs\dev\lib\site-packages\ipykernel_launcher.py:3: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.
  This is separate from the ipykernel package so we can avoid doing imports until

Step 4: Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the cumulative returns of the ETF portfolio.¶

In [16]:
# Use the average daily returns provided by the etf_portfolio_returns DataFrame 
# to calculate the cumulative returns
etf_cumulative_returns = (1 + etf_portfolio_returns['average_daily_returns']).cumprod() - 1

#Combined with earlier 'time_dataset' to give both x and y clearer labels in resulting hvplot
etf_cumulative_returns = pd.concat([time_dataset, etf_cumulative_returns], axis=1, join='inner')
etf_cumulative_returns_cols = ['time', 'cumulative_daily_returns']
etf_cumulative_returns.columns = etf_cumulative_returns_cols
#Got rid of time component to make labels clearer in graph
etf_cumulative_returns['time'] = pd.to_datetime(etf_cumulative_returns['time'])

# Display the final cumulative return value
etf_cumulative_returns
Out[16]:
time cumulative_daily_returns
0 2016-12-16 -0.007038
1 2016-12-19 -0.008245
2 2016-12-20 0.000251
3 2016-12-21 -0.000754
4 2016-12-22 -0.008990
... ... ...
994 2020-11-30 3.374534
995 2020-12-01 3.357078
996 2020-12-02 3.329679
997 2020-12-03 3.378371
998 2020-12-04 3.418250

999 rows × 2 columns

Step 5: Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶

In [20]:
# Using hvplot, create an interactive line plot that visualizes the ETF portfolios cumulative return values.
etf_cumulative_returns.hvplot(
    x='time',
    xlabel='Date', 
    ylabel='Cumulative Returns', 
    title='ETF Portfolio Cumulative Returns',
    frame_width=700,
    frame_height=300,
    rot=90
)
Out[20]:
In [ ]: